﻿
USE MASTER
GO
DROP DATABASE TY4591_BRD
GO
CREATE DATABASE TY4591_BRD
GO
USE TY4591_BRD
GO


















/*
--更新每人累计总功勋
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'prUpdateCharPointAll')
	DROP PROCEDURE prUpdateCharPointAll
GO
CREATE PROCEDURE prUpdateCharPointAll 	
	@CharID BIGINT,
	@PointTotal INT,
	@PointKill INT,
	@PointHack INT,
	@PointShare INT,
	@LogDate DATETIME
--WITH ENCRYPTION
AS
	EXEC prCreateCharBaseBRD @CharID
	
	DECLARE @SQLs NVARCHAR(1000)
	DECLARE @TableName VARCHAR(100)
	SET @TableName = 'tbCharBaseBRD_' + CONVERT (VARCHAR(10), (1 + @CharID/100000) * 100000)

	BEGIN TRANSACTION	
	--每人累计总功勋	
	SET @SQLs = 'IF NOT EXISTS (SELECT 1 FROM '+ @TableName +' WHERE CharID=' + CONVERT(NVARCHAR(20), @CharID) + ')'
	SET @SQLs = @SQLs + ' BEGIN '
	SET @SQLs = @SQLs + ' INSERT INTO '+ @TableName +' ( CharID, PointTotal, PointKill, PointHack, PointShare, LogTime ) '
	SET @SQLs = @SQLs + ' VALUES(' + CONVERT(VARCHAR(20), @CharID) + ', '+ CONVERT(VARCHAR(20),@PointTotal) +',' + CONVERT(VARCHAR(20),@PointKill)+ ',' + CONVERT(VARCHAR(20),@PointHack)+ ',' + CONVERT(VARCHAR(20),@PointShare) +',''' + CONVERT(VARCHAR(20),@LogDate) + ''')'
	SET @SQLs = @SQLs + ' END '  
	SET @SQLs = @SQLs + ' ELSE '  
	SET @SQLs = @SQLs + ' BEGIN '	
	SET @SQLs = @SQLs + ' UPDATE '+ @TableName +' SET PointTotal=PointTotal+'+CONVERT(VARCHAR(20),@PointTotal) +', PointKill=PointKill+' + CONVERT(VARCHAR(20),@PointKill)+ ', PointHack=PointHack+' + CONVERT(VARCHAR(20),@PointHack) +', PointShare=PointShare+' + CONVERT(VARCHAR(20),@PointShare)
	SET @SQLs = @SQLs + ', LogTime=''' + CONVERT(VARCHAR(20),@LogDate) + ''''
	SET @SQLs = @SQLs + ' WHERE CharID= '+ CONVERT(VARCHAR(20), @CharID)
	SET @SQLs = @SQLs + ' END '  		
	EXEC (@SQLs)
	COMMIT TRANSACTION

GO

--更新每人累计总击杀
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'prUpdateCharKillAll')
	DROP PROCEDURE prUpdateCharKillAll
GO
CREATE PROCEDURE prUpdateCharKillAll 	
	@CharID BIGINT,
	@KillTotal INT,
	@KillRobot INT,
	@KillExRobot INT,
	@KillVehicle INT,
	@KillTank INT,
	@KillPlane INT,
	@LogDate DATETIME
--WITH ENCRYPTION
AS
	EXEC prCreateCharBaseBRD @CharID
	
	DECLARE @SQLs NVARCHAR(1000)
	DECLARE @TableName VARCHAR(100)
	SET @TableName = 'tbCharBaseBRD_' + CONVERT (VARCHAR(10), (1 + @CharID/100000) * 100000)

	BEGIN TRANSACTION	
	--每人累计总击杀	
	SET @SQLs = 'IF NOT EXISTS (SELECT 1 FROM '+ @TableName +' WHERE CharID=' + CONVERT(NVARCHAR(20), @CharID) + ')'
	SET @SQLs = @SQLs + ' BEGIN '
	SET @SQLs = @SQLs + ' INSERT INTO '+ @TableName +' ( CharID, KillTotal, KillRobot,KillExRobot,KillVehicle,KillTank,KillPlane, LogTime ) '
	SET @SQLs = @SQLs + ' VALUES(' + CONVERT(VARCHAR(20), @CharID) + ', '+ CONVERT(VARCHAR(20),@KillTotal) +',' + CONVERT(VARCHAR(20),@KillRobot) +',' + CONVERT(VARCHAR(20),@KillExRobot) +',' + CONVERT(VARCHAR(20),@KillVehicle) +',' + CONVERT(VARCHAR(20),@KillTank) +',' + CONVERT(VARCHAR(20),@KillPlane) +',''' + CONVERT(VARCHAR(20),@LogDate) + ''')'
	SET @SQLs = @SQLs + ' END '  
	SET @SQLs = @SQLs + ' ELSE '  
	SET @SQLs = @SQLs + ' BEGIN '	
	SET @SQLs = @SQLs + ' UPDATE '+ @TableName +' SET KillTotal=KillTotal+'+CONVERT(VARCHAR(20),@KillTotal) +', KillRobot=KillRobot+' + CONVERT(VARCHAR(20),@KillRobot)+ ', KillExRobot=KillExRobot+' + CONVERT(VARCHAR(20),@KillExRobot)+ ', KillVehicle=KillVehicle+' + CONVERT(VARCHAR(20),@KillVehicle)+ ', KillTank=KillTank+' + CONVERT(VARCHAR(20),@KillTank)+ ', KillPlane=KillPlane+' + CONVERT(VARCHAR(20),@KillPlane)
	SET @SQLs = @SQLs + ', LogTime=''' +  CONVERT(VARCHAR(20),@LogDate) + ''''
	SET @SQLs = @SQLs + ' WHERE CharID= '+ CONVERT(VARCHAR(20), @CharID)
	SET @SQLs = @SQLs + ' END '  		
	EXEC (@SQLs)
	COMMIT TRANSACTION

GO

--更新每月每人获得功勋（含累计总）
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'prUpdateCharPointMonthly')
	DROP PROCEDURE prUpdateCharPointMonthly
GO
CREATE PROCEDURE prUpdateCharPointMonthly 	
	@CharID BIGINT,
	@PointTotal INT,
	@PointKill INT,
	@PointHack INT,
	@PointShare INT,
	@LogDate DATETIME
--WITH ENCRYPTION
AS
	DECLARE @SQLs NVARCHAR(1000)
	DECLARE @TableName VARCHAR(100)
SET @TableName = 'tbCharPointMonthly_' + CAST(YEAR(@LogDate) AS VARCHAR(4)) + '_' + CAST(MONTH(@LogDate) AS VARCHAR(2))

IF NOT EXISTS (SELECT 1 FROM @TableName WHERE CharID=@CharID)

	BEGIN TRANSACTION
	--每月每人功勋	
	SET @TableName = 'tbCharPointMonthly_' + CAST(YEAR(@LogDate) AS VARCHAR(4)) + '_' + CAST(MONTH(@LogDate) AS VARCHAR(2))
	SET @SQLs = 'IF NOT EXISTS (SELECT 1 FROM '+ @TableName +' WHERE CharID=' + CONVERT(NVARCHAR(20), @CharID) + ')'
	SET @SQLs = @SQLs + ' BEGIN '
	SET @SQLs = @SQLs + ' INSERT INTO '+ @TableName +' ( CharID, PointTotal, PointKill, PointHack, PointShare, LogTime ) '
	SET @SQLs = @SQLs + ' VALUES(' + CONVERT(VARCHAR(20), @CharID) + ', '+ CONVERT(VARCHAR(20),@PointTotal) +',' + CONVERT(VARCHAR(20),@PointKill) +',' + CONVERT(VARCHAR(20),@PointHack) +',' + CONVERT(VARCHAR(20),@PointShare) +',''' + CONVERT(VARCHAR(20),@LogDate) + ''')'
	SET @SQLs = @SQLs + ' END '  
	SET @SQLs = @SQLs + ' ELSE '  
	SET @SQLs = @SQLs + ' BEGIN '	
	SET @SQLs = @SQLs + ' UPDATE '+ @TableName +' SET PointTotal=PointTotal+'+CONVERT(VARCHAR(20),@PointTotal) +', PointKill=PointKill+' + CONVERT(VARCHAR(20),@PointKill) +', PointHack=PointHack+' + CONVERT(VARCHAR(20),@PointHack) +', PointShare=PointShare+' + CONVERT(VARCHAR(20),@PointShare)
	SET @SQLs = @SQLs + ', LogTime=''' +  CONVERT(VARCHAR(20),@LogDate) + ''''
	SET @SQLs = @SQLs + ' WHERE CharID= '+ CONVERT(VARCHAR(20), @CharID)
	SET @SQLs = @SQLs + ' END '  		
	EXEC (@SQLs)
	COMMIT TRANSACTION

	EXEC prUpdateCharPointAll @CharID, @PointTotal, @PointKill, @PointHack, @PointShare, @LogDate
GO

--更新每月每人击杀（含累计总）
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'prUpdateCharKillMonthly')
	DROP PROCEDURE prUpdateCharKillMonthly
GO
CREATE PROCEDURE prUpdateCharKillMonthly 	
	@CharID BIGINT,
	@KillTotal INT,
	@KillRobot INT,
	@KillExRobot INT,
	@KillVehicle INT,
	@KillTank INT,
	@KillPlane INT,
	@LogDate DATETIME
--WITH ENCRYPTION
AS
	DECLARE @SQLs NVARCHAR(1000)
	DECLARE @TableName VARCHAR(100)

	BEGIN TRANSACTION
	--每月每人功勋	
	SET @TableName = 'tbCharKillMonthly_' + CAST(YEAR(@LogDate) AS VARCHAR(4)) + '_' + CAST(MONTH(@LogDate) AS VARCHAR(2))
	SET @SQLs = 'IF NOT EXISTS (SELECT 1 FROM '+ @TableName +' WHERE CharID=' + CONVERT(NVARCHAR(20), @CharID) + ')'
	SET @SQLs = @SQLs + ' BEGIN '
	SET @SQLs = @SQLs + ' INSERT INTO '+ @TableName +' ( CharID, KillTotal, KillRobot,KillExRobot,KillVehicle,KillTank,KillPlane, LogTime ) '
	SET @SQLs = @SQLs + ' VALUES(' + CONVERT(VARCHAR(20), @CharID) + ', '+ CONVERT(VARCHAR(20),@KillTotal) +',' + CONVERT(VARCHAR(20),@KillRobot) +',' + CONVERT(VARCHAR(20),@KillExRobot)+ ',' + CONVERT(VARCHAR(20),@KillVehicle) +',' + CONVERT(VARCHAR(20),@KillTank) +',' + CONVERT(VARCHAR(20),@KillPlane) +',''' + CONVERT(VARCHAR(20),@LogDate) + ''')'
	SET @SQLs = @SQLs + ' END '  
	SET @SQLs = @SQLs + ' ELSE '  
	SET @SQLs = @SQLs + ' BEGIN '	
	SET @SQLs = @SQLs + ' UPDATE '+ @TableName +' SET KillTotal=KillTotal+'+CONVERT(VARCHAR(20),@KillTotal) +', KillRobot=KillRobot+' + CONVERT(VARCHAR(20),@KillRobot)+ ', KillExRobot=KillExRobot+' + CONVERT(VARCHAR(20),@KillExRobot)+ ', KillVehicle=KillVehicle+' + CONVERT(VARCHAR(20),@KillVehicle)+ ', KillTank=KillTank+' + CONVERT(VARCHAR(20),@KillTank)+ ', KillPlane=KillPlane+' + CONVERT(VARCHAR(20),@KillPlane)
	SET @SQLs = @SQLs + ', LogTime=''' +  CONVERT(VARCHAR(20),@LogDate) + ''''
	SET @SQLs = @SQLs + ' WHERE CharID= '+ CONVERT(VARCHAR(20), @CharID)
	SET @SQLs = @SQLs + ' END ' 		
	EXEC (@SQLs)
	COMMIT TRANSACTION

	EXEC prUpdateCharKillAll @CharID, @KillTotal, @KillRobot, @KillExRobot, @KillVehicle, @KillTank, @KillPlane, @LogDate
GO
*/




/*

--创建角色基础数据表
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'prCreateCharBaseBRD')
	DROP PROCEDURE prCreateCharBaseBRD
GO
CREATE PROCEDURE prCreateCharBaseBRD 	
	@CharID BIGINT
AS
	DECLARE @SQLs NVARCHAR(1000)
	DECLARE @TableName VARCHAR(100)
	SET @TableName = 'tbCharBaseBRD_' + CONVERT (VARCHAR(10), (1 + @CharID/100000) * 100000)
	
	BEGIN TRANSACTION	
	IF NOT EXISTS (SELECT 1 FROM Sysobjects WHERE Type = 'U' AND Name = @TableName)
	BEGIN 
		SET @SQLs = 'CREATE TABLE ' + @TableName + '
		(
			CharID BIGINT NOT NULL DEFAULT 0,
			PointTotal BIGINT NOT NULL DEFAULT 0,	
			PointKill BIGINT NOT NULL DEFAULT 0,	
			PointHack BIGINT NOT NULL DEFAULT 0,	
			PointShare BIGINT NOT NULL DEFAULT 0,
			PointTask BIGINT NOT NULL DEFAULT 0,
			KillTotal BIGINT NOT NULL DEFAULT 0,	
			KillRobot BIGINT NOT NULL DEFAULT 0,
			KillExRobot BIGINT NOT NULL DEFAULT 0,	
			KillVehicle BIGINT NOT NULL DEFAULT 0,	
			KillTank BIGINT NOT NULL DEFAULT 0,	
			KillPlane BIGINT NOT NULL DEFAULT 0,	
			Ex1 BIGINT NOT NULL DEFAULT 0,
			Ex2 BIGINT NOT NULL DEFAULT 0,
			Ex3 BIGINT NOT NULL DEFAULT 0,
			LogTime DATETIME NOT NULL
		)'
		EXEC (@SQLs)
	END
	COMMIT TRANSACTION
		

	--每人使用武器击杀?????????????????
	SET @TableName = 'tbCharWeaponKill_' + (CAST ((1 + @CharID/10000) * 10000) AS VARCHAR(10))
	IF NOT EXISTS (SELECT 1 FROM Sysobjects WHERE Type = 'U' AND Name = @TableName)
	BEGIN 
		SET @SQLs = 'CREATE TABLE ' + @TableName + '
		(
			CharID BIGINT NOT NULL DEFAULT 0,
			TargetID BIGINT NOT NULL DEFAULT 0,	
			KillNum BIGINT NOT NULL DEFAULT 0,	
			Ex1 BIGINT NOT NULL DEFAULT 0,
			Ex2 BIGINT NOT NULL DEFAULT 0,
			Ex3 BIGINT NOT NULL DEFAULT 0,
			LogTime DATETIME NOT NULL
		)'
		EXEC (@SQLs)
	END
	
	--每人击杀目标角色
	SET @TableName = 'tbCharKillTarget_' + (CAST ((1 + @CharID/10000) * 10000) AS VARCHAR(10))
	IF NOT EXISTS (SELECT 1 FROM Sysobjects WHERE Type = 'U' AND Name = @TableName)
	BEGIN 
		SET @SQLs = 'CREATE TABLE ' + @TableName + '
		(
			CharID BIGINT NOT NULL DEFAULT 0,
			TargetID BIGINT NOT NULL DEFAULT 0,	
			KillNum BIGINT NOT NULL DEFAULT 0,	
			Ex1 BIGINT NOT NULL DEFAULT 0,
			Ex2 BIGINT NOT NULL DEFAULT 0,
			Ex3 BIGINT NOT NULL DEFAULT 0,
			LogTime DATETIME NOT NULL
		)'
		EXEC (@SQLs)
	END
*/
/*
--角色获得功勋
IF EXISTS (SELECT * FROM Sysobjects WHERE Type = 'U' AND Name = 'CharacterPoint')
	DROP TABLE CharacterPoint
GO
CREATE TABLE CharacterPoint
(
	DataID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	BattlefieldID BIGINT NOT NULL DEFAULT 0,
	LogTime DATETIME NOT NULL,
	LocX FLOAT NOT NULL DEFAULT 0,
	LocY FLOAT NOT NULL DEFAULT 0,
	LocZ FLOAT NOT NULL DEFAULT 0,
	CharID BIGINT NOT NULL DEFAULT 0,
	EventID BIGINT NOT NULL DEFAULT 0,
	EventData NVARCHAR(200),
)
GO

--角色基本时间，包括进出战场、重生、死亡、担任队长等
IF EXISTS (SELECT * FROM Sysobjects WHERE Type = 'U' AND Name = 'CharacterEvent')
	DROP TABLE CharacterEvent
GO
CREATE TABLE CharacterEvent
(
	DataID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	BattlefieldID BIGINT NOT NULL DEFAULT 0,
	LogTime DATETIME NOT NULL,
	LocX FLOAT NOT NULL DEFAULT 0,
	LocY FLOAT NOT NULL DEFAULT 0,
	LocZ FLOAT NOT NULL DEFAULT 0,
	CharID BIGINT NOT NULL DEFAULT 0,
	EventID BIGINT NOT NULL DEFAULT 0,
	EventData NVARCHAR(200),
)
GO

--角色基本时间，包括进出战场、重生、死亡、担任队长等
IF EXISTS (SELECT * FROM Sysobjects WHERE Type = 'U' AND Name = 'CharacterEvent')
	DROP TABLE CharacterEvent
GO
CREATE TABLE CharacterEvent
(
	DataID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	BattlefieldID BIGINT NOT NULL DEFAULT 0,
	LogTime DATETIME NOT NULL,
	LocX FLOAT NOT NULL DEFAULT 0,
	LocY FLOAT NOT NULL DEFAULT 0,
	LocZ FLOAT NOT NULL DEFAULT 0,
	CharID BIGINT NOT NULL DEFAULT 0,
	EventID BIGINT NOT NULL DEFAULT 0,
	EventData NVARCHAR(200),
)
GO

IF EXISTS (SELECT * FROM Sysobjects WHERE Type = 'U' AND Name = 'HackData')
	DROP TABLE HackData
GO
CREATE TABLE HackData
(
	DataID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	BattlefieldID BIGINT NOT NULL DEFAULT 0,
	LogTime DATETIME NOT NULL,
	LocX FLOAT NOT NULL DEFAULT 0,
	LocY FLOAT NOT NULL DEFAULT 0,
	LocZ FLOAT NOT NULL DEFAULT 0,
	CharID BIGINT NOT NULL DEFAULT 0,
	EventID BIGINT NOT NULL DEFAULT 0,
	EventData NVARCHAR(200),
	FlyweightID BIGINT NOT NULL DEFAULT 0,
	EstID BIGINT NOT NULL DEFAULT 0,
)
GO

IF EXISTS (SELECT * FROM Sysobjects WHERE Type = 'U' AND Name = 'CharacterUseWeapon')
	DROP TABLE CharacterUseWeapon
GO
CREATE TABLE CharacterUseWeapon
(
	DataID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	CharID BIGINT NOT NULL DEFAULT 0,
	EditTime DATETIME NOT NULL DEFAULT GETDATE(),
)
GO

IF EXISTS (SELECT * FROM Sysobjects WHERE Type = 'U' AND Name = 'CharacterKillObj')
	DROP TABLE CharacterKillObj
GO
CREATE TABLE CharacterKillObj
(
	DataID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	CharID BIGINT NOT NULL DEFAULT 0,
	EditTime DATETIME NOT NULL DEFAULT GETDATE(),
)
GO

/*
IF EXISTS (SELECT * FROM Sysobjects WHERE Type = 'U' AND Name = 'WeaponData')
	DROP TABLE WeaponData
GO
CREATE TABLE WeaponData
(
	DataID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	BattlefieldID BIGINT NOT NULL DEFAULT 0,
	LogTime DATETIME NOT NULL,
	LocX FLOAT NOT NULL DEFAULT 0,
	LocY FLOAT NOT NULL DEFAULT 0,
	LocZ FLOAT NOT NULL DEFAULT 0,
	CharID BIGINT NOT NULL DEFAULT 0,
	EventID BIGINT NOT NULL DEFAULT 0,
	EventData NVARCHAR(200),
	FlyweightID BIGINT NOT NULL DEFAULT 0,
	UsedSeconds BIGINT NOT NULL DEFAULT 0,
	BulletCount BIGINT NOT NULL DEFAULT 0,
	HitCount BIGINT NOT NULL DEFAULT 0,
)
GO
*/